/**
 * 
 */
package org.gov.vis.master.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.master.dto.StaffDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.DU;
import org.gov.vis.util.Util;

/**
 * @author Vishwanath
 *
 */
public class StaffDaoImpl implements StaffDao{

	/**
	 *Method called to either Add or Edit a Staff
	 */
	@Override
	public void addOrEditStaff(final StaffDto _staffDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			if(_staffDto.getStaffId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_STAFF);
				pStmt.setString(1, _staffDto.getCode());
				pStmt.setString(2, _staffDto.getName());
				pStmt.setString(3, _staffDto.getFatherName());
				pStmt.setString(4, DU.format(_staffDto.getDob(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(5, _staffDto.getQualificatiion());
				pStmt.setString(6, _staffDto.getDesignation());
				pStmt.setInt(7, Integer.valueOf(_staffDto.getEmploymentType()));
				pStmt.setString(8, DU.format(_staffDto.getDoj(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(9, _staffDto.getVillage());
				pStmt.setString(10, _staffDto.getMandal());
				pStmt.setString(11, _staffDto.getDistrict());
				pStmt.setLong(12, _staffDto.getStaffId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_STAFF);
				pStmt.setString(1, _staffDto.getCode());
				pStmt.setString(2, _staffDto.getName());
				pStmt.setString(3, _staffDto.getFatherName());
				pStmt.setString(4, DU.format(_staffDto.getDob(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(5, _staffDto.getQualificatiion());
				pStmt.setString(6, _staffDto.getDesignation());
				pStmt.setInt(7, Integer.valueOf(_staffDto.getEmploymentType()));
				pStmt.setString(8, DU.format(_staffDto.getDoj(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(9, _staffDto.getVillage());
				pStmt.setString(10, _staffDto.getMandal());
				pStmt.setString(11, _staffDto.getDistrict());
			}

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a Staff
	 */
	@Override
	public void deleteStaff(final long _staffId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_STAFF);
			pStmt.setLong(1, _staffId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of Staffs based on the passed Staff 
	 */
	@Override
	public List<StaffDto> getStaffs(final StaffDto _staffDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<StaffDto> _staffList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_STAFF);
			if(_staffDto.getStaffId() > 0){
				pStmt.setLong(1, _staffDto.getStaffId());
				pStmt.setLong(2, _staffDto.getStaffId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_staffDto.getCode() != null && !_staffDto.getCode().trim().equals("")){
				pStmt.setString(3, _staffDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_staffDto.getName() != null && !_staffDto.getName().trim().equals("")){
				pStmt.setString(4, _staffDto.getName());
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_staffDto.getStaffId() > 0){
				pStmt.setLong(5, Long.parseLong(_staffDto.getEmploymentType()));
				pStmt.setLong(6, Long.parseLong(_staffDto.getEmploymentType()));
			}
			else{
				pStmt.setLong(5, Long.MIN_VALUE);
				pStmt.setLong(6, Long.MAX_VALUE);
			}
			
			rsSet = pStmt.executeQuery();
			
			_staffList = populateStaffs(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _staffList;
	}//end getStaffs


	/**
	 * Method called to populate the Staff records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<StaffDto> populateStaffs(final ResultSet _rsSet)throws Exception{
		List<StaffDto> _staffList = null;
		StaffDto _staffDto = null;
		
		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_staffList = new ArrayList<StaffDto>();
			}
			//initialize the Staff
			_staffDto = new StaffDto();
			_staffDto.setStaffId(_rsSet.getLong(DBConstants.COL_STAFF_ID));
			_staffDto.setCode(_rsSet.getString(DBConstants.COL_STAFF_CODE));
			_staffDto.setDesignation(_rsSet.getString(DBConstants.COL_STAFF_DESIGNATION));
			_staffDto.setDistrict(_rsSet.getString(DBConstants.COL_STAFF_DISTRICT));
			_staffDto.setDob(_rsSet.getDate(DBConstants.COL_STAFF_DOJ));
			_staffDto.setDoj(_rsSet.getDate(DBConstants.COL_STAFF_DOJ));
			_staffDto.setFatherName(_rsSet.getString(DBConstants.COL_STAFF_FATHER_NAME));
			_staffDto.setEmploymentType(_rsSet.getString(DBConstants.COL_STAFF_JOB_TYPE));
			_staffDto.setMandal(_rsSet.getString(DBConstants.COL_STAFF_MANDAL));
			_staffDto.setName(_rsSet.getString(DBConstants.COL_STAFF_NAME));
			_staffDto.setQualificatiion(_rsSet.getString(DBConstants.COL_STAFF_QUALIFICATION));
			_staffDto.setVillage(_rsSet.getString(DBConstants.COL_STAFF_VILLAGE));
			//add the current Object to the list
			_staffList.add(_staffDto);
			
		}
		return _staffList;
	}//end populateStaffs

	/**
	 * Method is called to search the Staffs based on the gopalamithra.
	 */
	@Override
	public List<StaffDto> searchStaffs(final StaffDto _staffDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<StaffDto> _staffList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_STAFF);
			if(_staffDto.getStaffId() > 0){
				pStmt.setLong(1, _staffDto.getStaffId());
				pStmt.setLong(2, _staffDto.getStaffId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_staffDto.getCode() != null && !_staffDto.getCode().trim().equals("")){
				pStmt.setString(3, _staffDto.getCode() + "%");
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_staffDto.getName() != null && !_staffDto.getName().trim().equals("")){
				pStmt.setString(4, _staffDto.getName());
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_staffDto.getStaffId() > 0){
				pStmt.setLong(5, Long.parseLong(_staffDto.getEmploymentType()));
				pStmt.setLong(6, Long.parseLong(_staffDto.getEmploymentType()));
			}
			else{
				pStmt.setLong(5, Long.MIN_VALUE);
				pStmt.setLong(6, Long.MAX_VALUE);
			}
			rsSet = pStmt.executeQuery();
			
			_staffList = populateStaffs(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _staffList;
	}//end searchStaffs

}//end class
